package com.codrim.cs.data.server.dao.mapper;

import com.codrim.cs.data.rpc.dto.param.TopAffiliateConversionParamDTO;
import com.codrim.cs.data.server.dao.entity.CooperatorsStatDO;
import com.codrim.cs.data.server.dao.entity.StatCsAdvAffDO;
import com.codrim.cs.data.server.dao.entity.TopAffiliateConversionDO;
import com.codrim.cs.data.server.dao.entity.unique.key.StatCsAdvAffUniqueKey;
import com.codrim.cs.data.server.dao.param.CooperatorsDayStatParam;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

/**
 * Created by Liang.Zhuge on 04/05/2017.
 */
@Mapper
public interface StatCsAdvAffMapper extends StatCsMapper<StatCsAdvAffDO, StatCsAdvAffUniqueKey> {
    String tableName = "e0000_day_stat_cs_adv_aff";

    @Select("<script>select " +
            "affiliate_id companyId," +
            "sum(click) clicks, " +
            "sum(uclick) uniqueClicks, " +
            "sum(gc) conversions, " +
            "sum(aprice) revenue " +
            "from e0000_day_stat_cs_adv_aff " +
            "where sdate between #{param.startDate} and #{param.endDate} and advertiser_id = #{param.companyId} and " +
            "affiliate_id in " +
            "<foreach item='item' index='index' collection='param.cooperatorIds' open='(' separator=',' close=')'>" +
            "#{item}" +
            "</foreach>" +
            "group by affiliate_id</script>")
    List<CooperatorsStatDO> cooperatorsAdvertiserStatData(@Param("param") CooperatorsDayStatParam param,
            @Param("sZone") String sZone);

    @Select("<script>select " +
            "advertiser_id companyId," +
            "sum(click) clicks, " +
            "sum(uclick) uniqueClicks, " +
            "sum(gc) conversions, " +
            "sum(aprice) revenue " +
            "from e0000_day_stat_cs_adv_aff " +
            "where sdate between #{param.startDate} and #{param.endDate} and affiliate_id = #{param.companyId} and " +
            "advertiser_id in " +
            "<foreach item='item' index='index' collection='param.cooperatorIds' open='(' separator=',' close=')'>" +
            "#{item}" +
            "</foreach>" +
            "group by advertiser_id</script>")
    List<CooperatorsStatDO> cooperatorsAffiliateStatData(@Param("param") CooperatorsDayStatParam param,
            @Param("sZone") String sZone);

    @Select("insert into " + tableName + " (" +
            "sdate," +
            "symbol," +
            "advertiser_id," +
            "affiliate_id," +
            "click," +
            "uclick," +
            "gc," +
            "conversion," +
            "aprice," +
            "pprice," +
            "caps," +
            "sc, " +
            "adv_advertiser_manager_id, " +
            "adv_affiliate_manager_id, " +
            "adv_advertiser_manager_department_id, " +
            "adv_affiliate_manager_department_id " +
            ") values (" +
            "#{param.sdate}," +
            "#{param.symbol}," +
            "#{param.advertiserId}," +
            "#{param.affiliateId}," +
            "#{param.click}," +
            "#{param.uclick}," +
            "#{param.gc}," +
            "#{param.conversion}," +
            "#{param.aprice}," +
            "#{param.pprice}," +
            "#{param.caps}," +
            "#{param.sc}, " +
            "#{param.advAdvertiserManagerId}, " +
            "#{param.advAffiliateManagerId}, " +
            "#{param.advAdvertiserManagerDepartmentId}, " +
            "#{param.advAffiliateManagerDepartmentId} " +
            ")")
    void insert(@Param("param") StatCsAdvAffDO entity, @Param("sZone") String sZone, @Param("timeDimesion") String timeDimesion);

    @Delete("delete from " + tableName + " where sdate = ${sdate}")
    void deleteSpecifiedDayReportData(@Param("sdate") int sdate, @Param("sZone") String zone);

    @Override
    @Select("<script> select " +
            "id, " +
            "sdate," +
            "symbol," +
            "advertiser_id advertiserId," +
            "affiliate_id affiliateId," +
            "click," +
            "uclick," +
            "gc," +
            "conversion," +
            "aprice," +
            "pprice," +
            "caps," +
            "sc, " +
            "adv_advertiser_manager_id, " +
            "adv_affiliate_manager_id, " +
            "adv_advertiser_manager_department_id, " +
            "adv_affiliate_manager_department_id " +
            "from " + tableName +
            " where sdate = #{param.sdate} " +
            "<choose>" +
            "<when test='param.advertiserId !=null'> and advertiser_id = #{param.advertiserId} </when>" +
            "<otherwise> and advertiser_id is null </otherwise>" +
            "</choose>" +
            "<choose>" +
            "<when test='param.affiliateId !=null'> and affiliate_id = #{param.affiliateId} </when>" +
            "<otherwise> and affiliate_id is null </otherwise>" +
            "</choose>" +
            "<choose>" +
            "<when test='param.advAdvertiserManagerId !=null'> and adv_advertiser_manager_id = #{param.advAdvertiserManagerId} </when>" +
            "<otherwise> and adv_advertiser_manager_id is null </otherwise>" +
            "</choose>" +
            "<choose>" +
            "<when test='param.advAffiliateManagerId !=null'> and adv_affiliate_manager_id = #{param.advAffiliateManagerId} </when>" +
            "<otherwise> and adv_affiliate_manager_id is null </otherwise>" +
            "</choose>" +
            "<choose>" +
            "<when test='param.symbol !=null'> and symbol = #{param.symbol} </when>" +
            "<otherwise> and symbol is null </otherwise>" +
            "</choose> limit 1 </script>")
    StatCsAdvAffDO findByKey(@Param("param") StatCsAdvAffUniqueKey key,
            @Param("sZone") String zone, @Param("timeDimesion") String timeDimesion);

    @Override
    @Update("update " + tableName + " set " +
            "symbol = #{values.symbol}," +
            "click = #{values.click}," +
            "uclick = #{values.uclick}," +
            "gc = #{values.gc}," +
            "conversion = #{values.conversion}," +
            "aprice = #{values.aprice}," +
            "pprice = #{values.pprice}," +
            "caps = #{values.caps}," +
            "sc = #{values.sc} " +
            " where id = #{id}")
    void updateById(@Param("values") StatCsAdvAffDO dayStatCsAdvAffDO,
            @Param("id") Integer id,
            @Param("sZone") String zone, @Param("timeDimesion") String timeDimesion);

    /**
     * 查询广告主下转化排行的渠道
     *
     * @param begin        检索范围：起始时间
     * @param end          检索范围：结果时间
     * @param zone         时区
     * @return 匹配到的转化排行渠道
     */
    @Select("<script> select affiliate_id affiliateId, sum(conversion) conversion "+
            "from " + tableName + " where advertiser_id = #{param.advertiserId} "+
            "and sdate between #{begin} and #{end} "+
            "<if test='param.hasAllPermission==false'>"+
                "  and (1>2 "+
                "<if test='param.advertiserManagerId != null'>"+
                "   or adv_advertiser_manager_id = #{param.advertiserManagerId} "+
                "</if>"+
                "<if test='param.affiliateManagerId != null'>"+
                "   or adv_affiliate_manager_id = #{param.affiliateManagerId} "+
                "</if>"+
                "<if test='param.advertiserManagerDepartmentIdSet != null and param.advertiserManagerDepartmentIdSet.size()!=0' >" +
                "    or adv_advertiser_manager_department_id in " +
                "    <foreach item='item' index='index' collection='param.advertiserManagerDepartmentIdSet' open='(' separator=',' close=')'>" +
                "       #{item}" +
                "    </foreach>" +
                "</if>" +
                "<if test='param.affiliateManagerDepartmentIdSet != null and param.affiliateManagerDepartmentIdSet.size()!=0' >" +
                "    or adv_affiliate_manager_department_id in " +
                "    <foreach item='item' index='index' collection='param.affiliateManagerDepartmentIdSet' open='(' separator=',' close=')'>" +
                "       #{item}" +
                "    </foreach>" +
                "</if> "+
                " )" +
            "</if>" +
            "group by affiliate_id order by sum(conversion) desc limit #{param.num} </script>")
    List<TopAffiliateConversionDO> topAffiliateConversions(@Param("param")TopAffiliateConversionParamDTO param, @Param("begin") Integer begin, @Param("end") Integer end, @Param("sZone") String zone);
}
